** Merge store price indices with covariates 
** JHL 

*************************************
** Set up workspace
*************************************
version 14.0
clear all
set more off

cd "${path_home}"
adopath + ../programs

** log using "${path_log}/c04_merge_PIq_stores_mw_0615", text replace

*************************************
** Start work here
*************************************

timer on 1 

*************************************
** [1] Merge with quarterly MW 
*************************************

use "${path_big_dta}/PI/PIq_stores_0615", clear


gen state=fips_state_code
gen statefips=fips_state_code
cap noi gen yq = yq(year,quarter)
gen quarterly_date = yq(year,quarter)

** Use VZ state MW data 
merge m:1 statefips quarterly_date using "${path_big_dta}/mw/VZ/VZ_state_stata/VZ_state_quarterly_fl.dta", keep(match match_update match_conflict) nogen update 

** Log prices 
gen logptvc=log(pl_tvc_nsub) 
gen logpvul=log(pl_vul_nsub) 
gen log_torn_ptvc = log(pt_yes_tvc)
gen log_torn_pvul = log(pt_yes_vul)

if 1 == 1 {
	foreach v of varlist pl_nsub pgl_tvc_nsub pfl_tvc_nsub pfl_tvc_nsub pl_tvc_nsub {
		cap noi gen l`v' = log(`v')
	}
}

** Log sales and real sales 
gen logtvct=log(tvc_t)
gen logsales = log(tvc_t) 

gen real_sales = tvc_t / pl_tvc_nsub 
gen logrealsales = log(tvc_t / pl_tvc_nsub) 

*************************************
** [2] Merge with controls and variables for interactions 
*************************************

** Merge in controls 
cap drop _merge
** 12-25, 1225 now 1286 (miami-dade) 
replace fips_county_code=86 if fips_state_code==12&fips_county_code==25

** Merge with state region
cap noi gen state = fips_state_code 
merge m:1 state using "${path_big_dta}/us_states/state_regions", keep(master match) nogen
** Merge with county population 
merge m:1 fips_state_code fips_county_code year using "${path_big_dta}/us_counties/county_pop_0015", keep(master match) nogen
** Merge with county level QCEW variables, e.g. employment, average weekly earnings
merge m:1 fips_state_code fips_county_code year quarter using "${path_big_dta}/qcew/qcew_county_0415", keep(master match) nogen
** Merge with county average earnings quantiles 
merge m:1 fips_state_code fips_county_code using "${path_big_dta}/qcew/qcew_county_wage_q24_0415", keep(master match) nogen 
** Merge with county x sector level QCEW variables, e.g. number of establishments, lq etc.
merge m:1 channel_code fips_state_code fips_county_code year quarter using "${path_big_dta}/qcew/qcew_DFM_0415", keep(master match) nogen

cap noi gen fips = fips_state_code * 1000 + fips_county_code

rename store_zip3 zip3 
** Merge with FHFA housing prices 
merge m:1 zip3 year quarter using "${path_big_dta}/fhfa/HPI_AT_3zip", keep(master match) nogen 

** Merge in ACS income distribution variables 
merge m:1 fips using "${path_big_dta}/acs/hh_income/dta/acs_hh_income_dist_q24_0915", keep(master match) nogen 

** Merge with LAUS labor force data, e.g. unemployment rate, by county 
merge m:1 year quarter fips_state_code fips_county_code using "${path_big_dta}/laus/laus_counties_q", keep( master match ) nogen

** Generate variables needed for controls and interactions

gen loghousingp_zip3_at = log(hp_zip3_at_nsa)
gen logunemp=log(unemployment_rate_county)
gen logpop=log(popestimate)
gen logaw=log(avg_wkly_wage_county)

** Kaitz index 
gen mw_over_avg_w_county = (mean_mw*40) / avg_wkly_wage_county 
drop kaitz_c
gen kaitz_c = mw_over_avg_w_county 

** QCEW store type establishment counts per population (concentration) 
gen log_est=log(qtrly_estabs)
gen est_pop=qtrly_estabs*100000/popestimate 
	la var est_pop "Establishments per 100K population"
gen log_est_pop=log(qtrly_estabs/popestimate)

** Indexing states
	** 10 states, ignore NJ (34) which only indexed in 2015
gen indexing_states = 1 if state==41|state==53|state==4|state==8|state==12|state==29|state==30|state==32|state==39|state==50
	replace indexing_states = 0 if state != . & indexing_states == . 

saveold "${path_big_dta}/PI/PIq_mw_0615_master", replace version(12)

 
*************************************
** Close workspace
*************************************
timer off 1
timer list 1
** log close

